﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using Microsoft.Data.Sqlite;
using Nlnet.Avalonia.Controls;
using System.Data;
using SQLitePCL;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.Text.RegularExpressions;
using 畜牧业审核程序国产.Models.ToolsClass;
using OpenQA.Selenium.DevTools.V126.DOM;


namespace 畜牧业审核程序国产.Models
{
    internal class MyDBOperator
    {
        //#if DEBUG
        //        public static readonly string assetsPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Assets");
        //        public static readonly string
        //          _dbPath = Path.Combine(assetsPath, "畜牧业数据库.db"); //需要把这个路径写成数据路径
        //#else
        //public static readonly string
        //            _dbPath = Path.Combine(MyDataFolderOperator.AuditProgramFolderPath, "畜牧业数据库.db"); //需要把这个路径写成数据路径
        //#endif

        public static readonly string
            _dbPath = Path.Combine(MyDataFolderOperator.AuditProgramFolderPath, "畜牧业数据库.db"); //需要把这个路径写成数据路径

        public string StrExeSql { get; set; }


        private MyDBOperator()
        {

        }
        private static class SingletonInstance
        {
            public static readonly MyDBOperator Instance = new MyDBOperator();
        }
        public static MyDBOperator DBOperatorInstance => SingletonInstance.Instance;

        /// <summary>
        /// 连接
        /// </summary>
        public void Connect()
        {
            try
            {
                using (SqliteConnection conn = new SqliteConnection($"Data Source={_dbPath}"))
                {
                    conn.Open();
                    Console.WriteLine("数据库连接成功！");
                }
            }
            catch (Exception ex)
            {
                MyMessagebox.Show($"数据库连接错误: {ex.Message}");
            }
        }

        /// <summary>
        /// 执行某条语句,这条sql语句是这个类的StrExeSql,需要先给strExeSql赋值,才可以执行这一条语句
        /// </summary>
        public void Execute()
        {
            try
            {
                using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
                using (SqliteCommand cmd = new SqliteCommand(StrExeSql, conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"执行sql语句出错,错误的信息为{ex.Message}");
                // MyMessagebox.Show($"执行SQL语句错误: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 执行外部传入的sql语句
        /// </summary>
        /// <param name="执行语句"></param>
        public void ExecuteBySql(string 执行语句)
        {
            try
            {
                using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
                using (SqliteCommand cmd = new SqliteCommand(执行语句, conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"执行sql语句出错,出错的方法为DBOperator的ExecuteBySql,错误语句为{执行语句},错误信息为{ex.Message}");
                //MyMessagebox.Show($"执行语句错误,内容为{ex.Message}");
                throw;
            }
        }

        public void ExecuteBySql(string sql, Dictionary<string, object> parameters = null)
        {
            try
            {
                using (SqliteConnection conn = new SqliteConnection($"Data Source={_dbPath}"))
                {
                    conn.Open();
                    using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                    {
                        if (parameters != null)
                        {
                            foreach (var param in parameters)
                            {
                                cmd.Parameters.AddWithValue(param.Key, param.Value);
                            }
                        }

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"执行SQL语句出错,出错的方法为DBOperator的ExecuteBySql,错误语句为{sql},错误信息为{ex.Message}");
                throw;
            }
        }
        /// <summary>
        /// 删除从外面传进来的datatable,如果某一行的某个列为空,那么删除这一整行
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="ColumnName"></param>
        public void DeleteNullRow(DataTable dataTable, string ColumnName)
        {
            if (dataTable != null)
            {
                for (int i = dataTable.Rows.Count - 1; i >= 0; i--)
                {
                    DataRow row = dataTable.Rows[i];
                    if (row[ColumnName] == DBNull.Value || String.IsNullOrEmpty(row[ColumnName].ToString()))
                    {
                        row.Delete();
                    }
                }

                dataTable.AcceptChanges();
            }
        }



        /// <summary>
        /// 根据sql语句从sqlite数据库中获取datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dataTable = new DataTable();
            try
            {
                using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
                {
                    conn.Open();
                    using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                    using (SqliteDataReader reader = cmd.ExecuteReader())
                    {
                        //while (reader.Read())
                        //{
                        //    // 创建列
                        //    for (int i = 0; i < reader.FieldCount; i++)
                        //    {
                        //        dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
                        //    }

                        //    // 读取数据
                        //    while (reader.Read())
                        //    {
                        //        DataRow row = dataTable.NewRow();
                        //        for (int i = 0; i < reader.FieldCount; i++)
                        //        {
                        //            row[i] = reader[i];
                        //        }
                        //        dataTable.Rows.Add(row);
                        //    }
                        //}
                        //return dataTable;
                        dataTable.Load(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"从数据库获取记录集出现错误,方法为DBOperator的GetDataTable方法,错误的语句是{sql},错误内容为{ex.Message}");
                // MyMessagebox.Show($"获取数据表错误,内容为{ex.Message}");
                // errorMsg = ex.Message;
                throw;
                return null;
            }
            // errorMsg = null;
            return dataTable;
        }

        public bool CreateOnlyColumnsNewTable(string templateTableName, string finalTableName)
        {
            try
            {

           
            //这里要做判断fromatableaname存不存在
            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
            {
                conn.Open();
                if (TableExists(conn, finalTableName))
                {
                    //删除表
                    DropTable(conn, finalTableName);
                }

                //CreateTable(conn, TableName, TemplateName);
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = $"CREATE TABLE \"{finalTableName}\" AS SELECT * FROM {templateTableName} WHERE 1=2;";
                    command.ExecuteNonQuery();

                  
                   
                }
                string[] addTables = new string[]
                {
                   
                    $"ALTER TABLE {finalTableName} ADD COLUMN 市代码 TEXT;",
                    $"ALTER TABLE {finalTableName} ADD COLUMN 县代码 TEXT;",
                    $"ALTER TABLE {finalTableName} ADD COLUMN 当季出栏数 TEXT;",
                    $"ALTER TABLE {finalTableName} ADD COLUMN 乡镇代码 TEXT;",
                };
                   
                   
                for (int i = 0; i < addTables.Length; i++)
                {
                    using (SqliteCommand cmd = new SqliteCommand(addTables[i], conn))
                    {
                        cmd.ExecuteNonQuery();
                    } 
                }
                return true;
            }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// 创建或删除并根据传进来的参数重新按照模板创建一张只有列名的空白表,并添加一个createat名的列
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="templateName"></param>
        public bool ResetOnlyColumnNamesTable(string TableName)
        {
            string TemplateName;
            if (Regex.IsMatch(TableName, "209"))
            {
                TemplateName = "A209模板";
            }
            else if (Regex.IsMatch(TableName, "210"))
            {
                TemplateName = "A210模板";
            }
            else if (Regex.IsMatch(TableName, "211"))
            {
                TemplateName = "A211模板";
            }
            else if (Regex.IsMatch(TableName, "212"))
            {
                TemplateName = "A212模板";
            }
            else if (TableName.Contains("A406"))
            {
                TemplateName = "A406模板";
            }
            else
            {
                return false;
            }

            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
            {
                conn.Open();
                if (TableExists(conn, TableName))
                {
                    //删除表
                    DropTable(conn, TableName);
                }

                //CreateTable(conn, TableName, TemplateName);
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = $"CREATE TABLE \"{TableName}\" AS SELECT * FROM {TemplateName} WHERE 1=2;";
                    command.ExecuteNonQuery();


                    // 添加一个新列，数据类型为字符串
                    string newColumnName = "CreatedAt";
                    command.CommandText = $"ALTER TABLE {TableName} ADD COLUMN {newColumnName} TEXT;";
                    command.ExecuteNonQuery();
                }


                return true;
            }
        }

        /// <summary>
        /// 判断sqlitge数据库里是否存在特定名称的表,有的话删除
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        private void DropTable(SqliteConnection connection, string tableName)
        {
            using var command = connection.CreateCommand();
            command.CommandText = $"DROP TABLE IF EXISTS {tableName};";
            command.ExecuteNonQuery();
        }

        private void CreateTable(SqliteConnection connection, string tableName, string templateName)
        {
            using var command = connection.CreateCommand();
            command.CommandText = $"CREATE TABLE \"{tableName}\" AS SELECT * FROM {templateName} WHERE 1=2;";
            command.ExecuteNonQuery();


            // 添加一个新列，数据类型为字符串
            string newColumnName = "CreatedAt";
            command.CommandText = $"ALTER TABLE {tableName} ADD COLUMN {newColumnName} TEXT;";
            command.ExecuteNonQuery();

            /**应该在导入数据的时候写

            // 获取当前时间并格式化为字符串
            string currentTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            // 插入当前时间到新列的第一行
            command.CommandText = $"INSERT INTO {tableName} ({newColumnName}) VALUES ('{currentTime}');";
            command.ExecuteNonQuery();
            **/
        }
        /// <summary>
        /// (创建表的时候)给表的CreatedAt增加一个创建时间
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        public void AddImportTime(SqliteConnection conn, string tableName)
        {
            // 获取当前时间并格式化为字符串
            string currentTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string AddTimeSql = $"UPDATE {tableName} SET CreatedAt = '{currentTime}' WHERE rowid = 1";
            using (SqliteCommand insertCommand = new SqliteCommand(AddTimeSql, conn))
            {
                // 插入当前时间到新列的第一行

                insertCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 检查表是否存在,返回布尔值
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private bool TableExists(SqliteConnection connection, string tableName)
        {
            using var command = connection.CreateCommand();
            command.CommandText = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{tableName}';";

            var result = command.ExecuteScalar();
            return result != null;
        }

        public DataTable GetSchema()
        {
            DataTable schemaTable = new DataTable();
            try
            {
                using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
                {
                    conn.Open();
                    schemaTable = conn.GetSchema();
                    return schemaTable;
                }
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"获取数据库架构错误,方法是GetSchema,内容为{ex.Message}");
                //MyMessagebox.Show($"获取数据库架构错误,内容为{ex.Message}");
                return null;
            }
        }
        /// <summary>
        /// 返回的是不存在的表名组成的集合
        /// </summary>
        /// <param name="tables"></param>
        /// <returns></returns>
        public List<string> CheckTablesExist(IEnumerable<string> tables)
        {
            List<string> unexistTables = new List<string>();
            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))

            {
                conn.Open();
                // 使用 sqlite_master 表来查询表是否存在

                foreach (var item in tables)
                {
                    string query = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{item}';";
                    using (var command = new SqliteCommand(query, conn))
                    {
                        var result = command.ExecuteScalar(); // 执行查询

                        // 若 result 为 null，表不存在；否则，表存在
                        if (result == null)
                        {
                            unexistTables.Add(item);
                        }
                    }
                }

                return unexistTables;
            }
        }
        /// <summary>
        /// 查询表是否存在,返回一个布尔值,如果表不存在,返回false;
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool CheckIfTableExists(string tableName)
        {
            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))

            {
                conn.Open();
                // 使用 sqlite_master 表来查询表是否存在
                string query = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{tableName}';";

                using (var command = new SqliteCommand(query, conn))
                {
                    var result = command.ExecuteScalar(); // 执行查询

                    // 若 result 为 null，表不存在；否则，表存在
                    return result != null;
                }
            }
        }
        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="TableName"></param>
        public void DeleteTable(string TableName)
        {
            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
            {
                conn.Open();
                if (TableExists(conn, TableName))
                {
                    //删除表
                    DropTable(conn, TableName);
                }
            }
        }
        /// <summary>
        /// 传进来一张表名,删了查询用表,并将传进来的表名复制到一个新的查询用表中
        /// </summary>
        /// <param name="WithYearAndSeasonTableName"></param>
        public void DeleteQueryTableThenCreateNewQueryTable(string WithYearAndSeasonTableName)
        {
            DeleteTable("查询用表");
            string sqlSelectIntoNewQueryTable = $"CREATE TABLE 查询用表 AS SELECT * FROM {WithYearAndSeasonTableName}";
            ExecuteBySql(sqlSelectIntoNewQueryTable);
        }
        public void DeleteQoQTableThenCreateNewQoQTable(string WithYearAndSeasonTableName)
        {
            DeleteTable("环比用表");
            ExecuteBySql($"CREATE TABLE 环比用表 AS SELECT * FROM {WithYearAndSeasonTableName}");
        }
        public void DeleteTotalTableThenCreateNewTotalTable(string totalTableName)
        {
            DeleteTable("全面用表");
            ExecuteBySql($"CREATE TABLE 全面用表 AS SELECT * FROM {totalTableName}");
        }
        ///// <summary> 这个方法不应该出现在这里
        ///// 根据传进来的当前年度当前季度、环比年度环比季度、同比年度同比季度，再在其前面加上“一套表”或者"统计云",把表的名称\导入时间\创建时间形成一个datatable并返回去
        ///// </summary>
        ///// <param name="stryearnow"></param>
        ///// <param name="strseasonnow"></param>
        ///// <param name="stryearpre"></param>
        ///// <param name="strseasonpre"></param>
        ///// <param name="stryearyoy"></param>
        ///// <param name="strseasonyoy"></param>
        ///// <returns></returns>
        //public DataTable ShowAllNeedTableMsgs(string stryearnow, string strseasonnow, string stryearpre,
        //    string strseasonpre, string stryearyoy, string strseasonyoy)
        //{
        //    DataTable DTOfTableMsgs = new DataTable();
        //    string[] AllNeedTableNames = null;
        //    if (Convert.ToUInt16(stryearnow.Substring(0, 4)) < 2025)//2024年及以前的季度,设置一套表4张彪
        //    //和一套表406和本季度的统计云
        //    {
        //        AllNeedTableNames = new string[]
        //        {
        //            $"一套表{stryearnow}{strseasonnow}A209", $"一套表{stryearnow}{strseasonnow}A210",
        //            $"一套表{stryearnow}{strseasonnow}A211", $"一套表{stryearnow}{strseasonnow}A212",
        //            $"一套表{stryearnow}{strseasonnow}A406",
        //            $"一套表{stryearpre}{strseasonpre}A209", $"一套表{stryearpre}{strseasonpre}A210",
        //            $"一套表{stryearpre}{strseasonpre}A211", $"一套表{stryearpre}{strseasonpre}A212",
        //            $"一套表{stryearpre}{strseasonpre}A406",
        //            $"一套表{stryearyoy}{strseasonyoy}A209", $"一套表{stryearyoy}{strseasonyoy}A210",
        //            $"一套表{stryearyoy}{strseasonyoy}A211", $"一套表{stryearyoy}{strseasonyoy}A212",
        //            $"一套表{stryearyoy}{strseasonyoy}A406",
        //            $"统计云{stryearnow}{strseasonnow}A209", $"统计云{stryearnow}{strseasonnow}A210",
        //            $"统计云{stryearnow}{strseasonnow}A211", $"统计云{stryearnow}{strseasonnow}A212",

        //            $"统计云{stryearpre}{strseasonpre}A209", $"统计云{stryearpre}{strseasonpre}A210",
        //            $"统计云{stryearpre}{strseasonpre}A211", $"统计云{stryearpre}{strseasonpre}A212",


        //        };
        //    }
        //    //2025年第一季度和第二季度的同比季度还是一套表的
        //    else if (Convert.ToUInt16(stryearnow.Substring(0, 4)) == 2025 && (strseasonnow.Equals("第一季度") || strseasonnow.Equals("第二季度")))
        //    {
        //        AllNeedTableNames = new string[]
        //       {
        //            $"统计云{stryearnow}{strseasonnow}A209", $"统计云{stryearnow}{strseasonnow}A210",
        //            $"统计云{stryearnow}{strseasonnow}A211", $"统计云{stryearnow}{strseasonnow}A212",
        //            $"一套表{stryearnow}{strseasonnow}A406",
        //            $"统计云{stryearpre}{strseasonpre}A209", $"统计云{stryearpre}{strseasonpre}A210",
        //            $"统计云{stryearpre}{strseasonpre}A211", $"统计云{stryearpre}{strseasonpre}A212",
        //            $"一套表{stryearpre}{strseasonpre}A406",
        //            $"一套表{stryearyoy}{strseasonyoy}A209", $"一套表{stryearyoy}{strseasonyoy}A210",
        //            $"一套表{stryearyoy}{strseasonyoy}A211", $"一套表{stryearyoy}{strseasonyoy}A212",
        //            $"一套表{stryearyoy}{strseasonyoy}A406",



        //       };
        //    }
        //    else//2025年三季度(其实二季度)以后的,国网全部是统计云
        //    {
        //        AllNeedTableNames = new string[]
        //      {
        //            $"统计云{stryearnow}{strseasonnow}A209", $"统计云{stryearnow}{strseasonnow}A210",
        //            $"统计云{stryearnow}{strseasonnow}A211", $"统计云{stryearnow}{strseasonnow}A212",
        //            $"一套表{stryearnow}{strseasonnow}A406",
        //            $"统计云{stryearpre}{strseasonpre}A209", $"统计云{stryearpre}{strseasonpre}A210",
        //            $"统计云{stryearpre}{strseasonpre}A211", $"统计云{stryearpre}{strseasonpre}A212",
        //            $"一套表{stryearpre}{strseasonpre}A406",
        //            $"统计云{stryearyoy}{strseasonyoy}A209", $"统计云{stryearyoy}{strseasonyoy}A210",
        //            $"统计云{stryearyoy}{strseasonyoy}A211", $"统计云{stryearyoy}{strseasonyoy}A212",
        //            $"一套表{stryearyoy}{strseasonyoy}A406",



        //      };
        //    }
        //    //string[] AllNeedTableNames = new string[]
        //    //{
        //    //    $"一套表{stryearnow}{strseasonnow}A209", $"一套表{stryearnow}{strseasonnow}A210",
        //    //    $"一套表{stryearnow}{strseasonnow}A211", $"一套表{stryearnow}{strseasonnow}A212",
        //    //    $"一套表{stryearnow}{strseasonnow}A406",
        //    //    $"一套表{stryearpre}{strseasonpre}A209", $"一套表{stryearpre}{strseasonpre}A210",
        //    //    $"一套表{stryearpre}{strseasonpre}A211", $"一套表{stryearpre}{strseasonpre}A212",
        //    //    $"一套表{stryearpre}{strseasonpre}A406",
        //    //    $"一套表{stryearyoy}{strseasonyoy}A209", $"一套表{stryearyoy}{strseasonyoy}A210",
        //    //    $"一套表{stryearyoy}{strseasonyoy}A211", $"一套表{stryearyoy}{strseasonyoy}A212",
        //    //    $"一套表{stryearyoy}{strseasonyoy}A406",
        //    //    $"统计云{stryearnow}{strseasonnow}A209", $"统计云{stryearnow}{strseasonnow}A210",
        //    //    $"统计云{stryearnow}{strseasonnow}A211", $"统计云{stryearnow}{strseasonnow}A212",

        //    //    $"统计云{stryearpre}{strseasonpre}A209", $"统计云{stryearpre}{strseasonpre}A210",
        //    //    $"统计云{stryearpre}{strseasonpre}A211", $"统计云{stryearpre}{strseasonpre}A212",

        //    //    $"统计云{stryearyoy}{strseasonyoy}A209", $"统计云{stryearyoy}{strseasonyoy}A210",
        //    //    $"统计云{stryearyoy}{strseasonyoy}A211", $"统计云{stryearyoy}{strseasonyoy}A212",

        //    //};
        //    DTOfTableMsgs = GetDataTable("select 表名称,导入时间,已导入的户数 from DataTable要用的字段 "); //手动创建的datatable显示不了,这里是获取标题
        //    // DTOfTableMsgs = new DataTable();
        //    //DTOfTableMsgs.Columns.Add("表名", typeof(string));
        //    //DTOfTableMsgs.Columns.Add("导入时间", typeof(string));
        //    foreach (string tablename in AllNeedTableNames)
        //    {
        //        int rowcount;
        //        string TableCreateTime = ReturnTableCreateTimeAndRowCount(tablename, out rowcount);
        //        DataRow tableMsgRow = DTOfTableMsgs.NewRow();
        //        tableMsgRow[0] = tablename;
        //        tableMsgRow[1] = TableCreateTime;
        //        tableMsgRow[2] = rowcount;
        //        DTOfTableMsgs.Rows.Add(tableMsgRow);
        //    }

        //    //DTOfTableMsgs.Columns[0].ColumnName = "表名";
        //    //DTOfTableMsgs.Columns[1].ColumnName = "是否导入";
        //    return DTOfTableMsgs;
        //}

        /// <summary>
        /// 要查找的表如果存在,返回创建时间和记录的数量
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="RowCount"></param>
        /// <returns></returns>
        public string ReturnTableCreateTimeAndRowCount(string TableName, out int RowCount)
        {
            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
            {
                conn.Open();
                try
                {
                    string sql = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{TableName}'";
                    using (SqliteCommand cmd = new SqliteCommand(sql, conn))
                    {
                        var result = cmd.ExecuteScalar();
                        if (result == null)
                        {
                            RowCount = -1;
                            return "未导入";
                        }

                        //如果表存在,获取表的创建时间
                        RowCount = GetTableRowCount(conn, TableName);
                        string sqlToGetCreateTime = $"SELECT \"CreatedAt\" FROM \"{TableName}\" LIMIT 1";
                        using (SqliteCommand cmdToGetCreatetime = new SqliteCommand(sqlToGetCreateTime, conn))
                        {
                            var CreateTime = cmdToGetCreatetime.ExecuteScalar();
                            if (CreateTime == null)
                            {
                                return "导入出错,请重新导入";
                            }
                            return CreateTime.ToString();
                        }
                    }


                    //DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, TableName, "TABLE" });

                    //if (schema.Rows.Count > 0)
                    //{
                    //    // 获取表的创建时间
                    //    RowCount = GetTableRowCount(conn, TableName);
                    //    DateTime createDate = (DateTime)schema.Rows[0]["DATE_CREATED"];
                    //    return createDate.ToString("yyyy-MM-dd HH:mm:ss"); // 转换为指定格式的字符串
                    //    //return "已导入";
                    //}
                    //else
                    //{
                    //    RowCount = 0;
                    //    return "未导入";
                    //}
                }
                catch (Exception ex)
                {
                    StaticLogger.SystemError($"获取数据库中表的创建时间和行数时出现错误,错误的表名为{TableName},错误内容为{ex.Message}");
                    //  MyMessagebox.Show($"执行SQL语句错误: {ex.Message}");
                    RowCount = 0;
                    return $"{TableName}查询错误";
                }
            }
        }
        /// <summary>
        /// 查询表在数据库中的记录条数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        private int GetTableRowCount(SqliteConnection conn, string TableName)
        {
            string sql = $"select count(*) from [{TableName}]";
            using (SqliteCommand cmd = new SqliteCommand(sql, conn))
            {
                try
                {
                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
        /// <summary>
        /// 找不到的话返回"没有值"
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public string GetValueOfAssignColumnNameInFirstRow(string tableName, string columnName)
        {
            using (var con = new SqliteConnection($"Data Source = {_dbPath}"))
            {
                con.Open();
                string querySql = $"SELECT \"{columnName}\" FROM \"{tableName}\" LIMIT 1";
                using (var command = new SqliteCommand(querySql, con))
                {
                    // 执行查询并获取结果
                    var result = command.ExecuteScalar();

                    if (result != null)
                    {
                        return result.ToString();
                    }
                    else
                    {
                        //if (GetTableRowCount(con, tableName) == -1)
                        //{
                        //    return "没有该表";
                        //}
                        //else
                        //{
                        //    return "该表没有值";
                        //}
                        return "没有值";
                    }
                }
            }
        }
        public int CountOfParameterInAssignedColumn(string parameter, string columnName, string tableName)
        {
            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
            {
                conn.Open();
                string query = $"SELECT COUNT(*) FROM \"{tableName}\" WHERE {columnName} = '{parameter}'";
                using (SqliteCommand command = new SqliteCommand(query, conn))
                {
                    object count = command.ExecuteScalar();
                    if (count != null)
                    {
                        return Convert.ToInt32(count);
                    }
                    else { return 0; }
                }

            }

        }


        public void MakeSureChosenTablesNotInDB(string[] ArrStrTableNames)
        {
            using (SqliteConnection conn = new SqliteConnection($"Data Source = {_dbPath}"))
            {
                conn.Open();

                foreach (string TableName in ArrStrTableNames)
                {
                    DropTable(conn, TableName);
                }

            }

        }
        public void SetDatatableToANewTable(DataTable dataTable, string tableName)
        {
            // SQLite 数据库文件路径
            string connectionString = $"Data Source={_dbPath}";

            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                //try
                //{
                //    conn.Open();

                //    // 创建新表
                //    string createTableQuery = $"CREATE TABLE {tableName} (";
                //    foreach (DataColumn column in dataTable.Columns)
                //    {
                //        string dataType = GetSqliteDataType(column.DataType);
                //        createTableQuery += $"{column.ColumnName} {dataType}, ";
                //    }
                //    createTableQuery = createTableQuery.TrimEnd(',', ' ');
                //    createTableQuery += ")";

                //    using (SqliteCommand createCmd = new SqliteCommand(createTableQuery, conn))
                //    {
                //        createCmd.ExecuteNonQuery();
                //    }

                //    // 插入数据
                //    foreach (DataRow row in dataTable.Rows)
                //    {
                //        string insertQuery = $"INSERT INTO {tableName} VALUES (";
                //        foreach (var item in row.ItemArray)
                //        {
                //            if (item is string)
                //            {
                //                insertQuery += $"@{item.ToString()}, ";
                //            }
                //            else
                //            {
                //                insertQuery += $"@{item.ToString()}, ";
                //            }
                //        }
                //        insertQuery = insertQuery.TrimEnd(',', ' ');
                //        insertQuery += ")";

                //        using (SqliteCommand insertCommand = new SqliteCommand(insertQuery, conn))
                //        {
                //            for (int i = 0; i < row.ItemArray.Length; i++)
                //            {
                //                insertCommand.Parameters.AddWithValue($"@{i}", row.ItemArray[i]);
                //            }
                //            insertCommand.ExecuteNonQuery();
                //        }
                //    }
                //}
                //catch (SqliteException ex)
                //{
                //    // 处理异常\记录日志
                //    StaticLogger.SystemError($"把datatable写入到数据库时出现错误,方法是SetDatatableToANewTable,出错的表名为{tableName},错误信息为:{ex.Message}");
                //    //Console.WriteLine($"Database operation failed: {ex.Message}");
                //}
                try
                {
                    conn.Open();

                    // 创建新表
                    string createTableQuery = $"CREATE TABLE {tableName} (";
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        string dataType = GetSqliteDataType(column.DataType);
                        createTableQuery += $"\"{column.ColumnName}\" {dataType}, ";
                    }
                    createTableQuery = createTableQuery.TrimEnd(',', ' ') + ")";

                    using (SqliteCommand createCmd = new SqliteCommand(createTableQuery, conn))
                    {
                        createCmd.ExecuteNonQuery();
                    }

                    // 插入数据
                    foreach (DataRow row in dataTable.Rows)
                    {
                        string insertQuery = $"INSERT INTO {tableName} ({string.Join(", ", dataTable.Columns.Cast<DataColumn>().Select(c => $"\"{c.ColumnName}\""))}) VALUES (";

                        for (int i = 0; i < row.ItemArray.Length; i++)
                        {
                            object item = row.ItemArray[i];
                            if (item == null || item is DBNull)
                            {
                                insertQuery += "NULL";
                            }
                            else if (item is string strValue)
                            {
                                // 对字符串进行适当的转义以防止 SQL 注入
                                strValue = strValue.Replace("'", "''");
                                insertQuery += $"'{strValue}'";
                            }
                            else
                            {
                                insertQuery += item.ToString();
                            }

                            if (i < row.ItemArray.Length - 1)
                            {
                                insertQuery += ", ";
                            }
                        }
                        insertQuery += ")";

                        using (SqliteCommand insertCommand = new SqliteCommand(insertQuery, conn))
                        {
                            insertCommand.ExecuteNonQuery();
                        }
                    }
                }
                catch (SqliteException ex)
                {
                    // 处理异常\记录日志
                    StaticLogger.SystemError($"把datatable写入到数据库时出现错误,方法是SetDatatableToANewTable,出错的表名为{tableName},错误信息为:{ex.Message}");
                    throw; // 重新抛出异常以便调用者知道发生了什么
                }
            }
        }

        // 获取 SQLite 数据类型的方法
        private string GetSqliteDataType(Type dataType)
        {
            if (dataType == typeof(int) || dataType == typeof(long) || dataType == typeof(short))
            {
                return "INTEGER";
            }
            else if (dataType == typeof(double) || dataType == typeof(float) || dataType == typeof(decimal))
            {
                return "REAL";
            }
            else if (dataType == typeof(string))
            {
                return "TEXT";
            }
            else if (dataType == typeof(DateTime))
            {
                return "TEXT";
            }
            else
            {
                return "BLOB";
            }
        }
        public string[] GetColumnNamesArr(DataTable dt)
        {
            string[] columnNames = dt.Columns.Cast<DataColumn>()
                    .Select(x => x.ColumnName)
                    .ToArray();
            return columnNames;
        }
        public DataTable MergeTablesFromDatabaseToDataTable(string[] tableNames)
        {
            if (tableNames == null || tableNames.Length == 0)
                throw new ArgumentException("Array of DataTables cannot be null or empty.");
            DataTable dataTable = new DataTable();
            string connectionString = $"Data Source={_dbPath}";
            try
            {
                using (SqliteConnection conn = new SqliteConnection(connectionString))
                {
                    conn.Open();

                    foreach (string tableName in tableNames)
                    {
                        // 动态生成 SQL 查询语句
                        //string query = $"SELECT * FROM [{tableName}]";

                        using (SqliteCommand command = new SqliteCommand($"select * from {tableName}", conn))
                        using (SqliteDataReader reader = command.ExecuteReader())
                        {

                            dataTable.Load(reader);

                        }
                    }
                    return dataTable;
                }
            }
            catch (Exception)
            {

                throw;
            }

        }
        public bool MergeTablesFromDatabaseToAMergeTableInDB(string[] tableNames, string mergeTableName)
        {
            if (tableNames == null || tableNames.Length == 0)
                throw new ArgumentException("Array of DataTables cannot be null or empty.");
            DataTable dataTable = new DataTable();
            List<DataTable> tables = new List<DataTable>();
            string connectionString = $"Data Source={_dbPath}";
            try
            {
                using (SqliteConnection conn = new SqliteConnection(connectionString))
                {
                    conn.Open();

                    foreach (string tableName in tableNames)
                    {
                        // 动态生成 SQL 查询语句
                        //string query = $"SELECT * FROM [{tableName}]";

                        using (SqliteCommand command = new SqliteCommand($"select * from {tableName}", conn))
                        using (SqliteDataReader reader = command.ExecuteReader())
                        {

                            dataTable.Load(reader);

                        }
                    }
                    DeleteTable(mergeTableName);
                    SetDatatableToANewTable(dataTable, mergeTableName);
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public bool MergeTablesFromDatabaseToAMergeTableInDB(string[] tableNames)
        {
            if (tableNames == null || tableNames.Length == 0)
                throw new ArgumentException("Array of DataTables cannot be null or empty.");
            DataTable dataTable = new DataTable();
            List<DataTable> tables = new List<DataTable>();
            string connectionString = $"Data Source={_dbPath}";
            try
            {
                using (SqliteConnection conn = new SqliteConnection(connectionString))
                {
                    conn.Open();

                    foreach (string tableName in tableNames)
                    {
                        // 动态生成 SQL 查询语句
                        //string query = $"SELECT * FROM [{tableName}]";

                        using (SqliteCommand command = new SqliteCommand($"select * from {tableName}", conn))
                        using (SqliteDataReader reader = command.ExecuteReader())
                        {

                            dataTable.Load(reader);

                        }
                    }
                    DeleteTable("一到本季度用表");
                    SetDatatableToANewTable(dataTable, "一到本季度用表");
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        //这方法太浪费时间了
        //public  bool MergeTablesFromDatabaseToAMergeTableInDB(string[] sqlsToGetDatatableFromDB)
        //{
        //    if (sqlsToGetDatatableFromDB == null || sqlsToGetDatatableFromDB.Length == 0)
        //        throw new ArgumentException("Array of DataTables cannot be null or empty.");

        //    List<DataTable> tables = new List<DataTable>();
        //    string connectionString = $"Data Source={_dbPath}";
        //    try
        //    {
        //        using (SqliteConnection conn = new SqliteConnection(connectionString))

        //        {
        //            conn.Open();

        //            foreach (string sqls in sqlsToGetDatatableFromDB)
        //            {
        //                // 动态生成 SQL 查询语句
        //                //string query = $"SELECT * FROM [{tableName}]";

        //                using (SqliteCommand command = new SqliteCommand(sqls, conn))
        //                using (SqliteDataReader reader = command.ExecuteReader())
        //                {
        //                    DataTable dataTable = new DataTable();
        //                    dataTable.Load(reader);
        //                    tables.Add(dataTable);
        //                }
        //            }
        //        }
        //        //return MergeMultipleTables(tables.ToArray());
        //        MergeMultipleTablesSimpleRowAddToNewTable(tables.ToArray());
        //        return true;
        //    }
        //    catch(Exception ex)
        //    {
        //        return false;
        //    }
        //}

        private bool MergeMultipleTablesSimpleRowAddToNewTable(DataTable[] tables)
        {
            DeleteTable("一到本季度用表");
            try
            {
                DataTable mergedDataTable = tables[0].Clone();
                foreach (DataTable datatable in tables)
                {
                    foreach (DataRow row in datatable.Rows)
                    {
                        mergedDataTable.ImportRow(row);
                    }
                }
                SetDatatableToANewTable(mergedDataTable, "一到本季度用表");
                return true;
            }
            catch (Exception e)
            {
                throw;
                return false;
            }
        }

        //    private DataTable MergeTables(DataTable[] tables)
        //    {
        //        // 检查输入是否为空
        //        if (tables == null || tables.Length == 0)
        //            throw new ArgumentException("Input tables array cannot be null or empty.");

        //        // 创建合并后的表，使用第一个表的结构
        //        DataTable result = tables[0].Copy();

        //        // 只保留前两列（第一列和第二列）
        //        result.Clear();
        //        result.Columns.Clear();

        //        // 复制前两列的列结构
        //        foreach (DataColumn column in tables[0].Columns.Cast<DataColumn>().Take(2))
        //        {
        //            result.Columns.Add(column.ColumnName, column.DataType);
        //        }

        //        // 为后续的 double 类型字段创建合并列
        //        for (int i = 2; i < tables[0].Columns.Count; i++)
        //        {
        //            result.Columns.Add(tables[0].Columns[i].ColumnName, typeof(double));
        //        }

        //        // 遍历每张表，并将后续列的 double 类型值相加
        //        foreach (DataRow row1 in tables[0].Rows)
        //        {
        //            string key1 = row1[0].ToString();
        //            string key2 = row1[1].ToString();

        //            // 创建新行
        //            DataRow newRow = result.NewRow();
        //            newRow[0] = key1;
        //            newRow[1] = key2;

        //            // 初始化一个临时数组来存储所有合并的值
        //            double[] sumValues = new double[tables[0].Columns.Count - 2];

        //            // 将第一张表的数值加入
        //            for (int i = 2; i < tables[0].Columns.Count; i++)
        //            {
        //                sumValues[i - 2] += Convert.ToDouble(row1[i]);
        //            }

        //            // 遍历其余的表，将每个表的数值相加
        //            foreach (var table in tables.Skip(1))
        //            {
        //                // 查找与当前行的前两列值匹配的行
        //                DataRow matchingRow = table.Rows.Cast<DataRow>().FirstOrDefault(r =>
        //                    r[0].ToString() == key1 && r[1].ToString() == key2);

        //                if (matchingRow != null)
        //                {
        //                    // 累加每个后续列的 double 类型数据
        //                    for (int i = 2; i < table.Columns.Count; i++)
        //                    {
        //                        sumValues[i - 2] += Convert.ToDouble(matchingRow[i]);
        //                    }
        //                }
        //            }

        //            // 将累加后的值放入新的行中
        //            for (int i = 2; i < result.Columns.Count; i++)
        //            {
        //                newRow[i] = sumValues[i - 2];
        //            }

        //            result.Rows.Add(newRow);
        //        }

        //        return result;
        //    }
        //    private  DataTable MergeMultipleTables(DataTable[] tables)
        //    {
        //        if (tables == null || tables.Length == 0)
        //            throw new ArgumentException("Array of DataTables cannot be null or empty.");


        //        //DataTable resultTable = tables[0].Copy();

        //        //// 创建一个字典用于快速查找和累加数据
        //        //var dataDict = new System.Collections.Generic.Dictionary<string, DataRow>();

        //        //foreach (var table in tables)
        //        //{
        //        //    foreach (DataRow row in table.Rows)
        //        //    {
        //        //        string key = row[0].ToString();

        //        //        if (!dataDict.ContainsKey(key))
        //        //        {
        //        //            // 如果结果表中还没有这个键，则添加新行
        //        //            DataRow newRow = resultTable.NewRow();
        //        //            newRow[0] = row[0];
        //        //            newRow[1] = row[1];
        //        //            dataDict[key] = newRow;
        //        //            resultTable.Rows.Add(newRow);
        //        //        }

        //        //        // 获取对应的结果行
        //        //        DataRow resultRow = dataDict[key];

        //        //        // 遍历后续各列并累加
        //        //        for (int i = 2; i < table.Columns.Count; i++)
        //        //        {
        //        //            double value = Convert.ToDouble(row[i]);
        //        //            resultRow[i] = Convert.ToDouble(resultRow[i]) + value;
        //        //        }
        //        //    }
        //        //}

        //        //return resultTable;
        //    }
    }
}